package data.dao.daoImplementation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpSession;

import data.dao.*;
import data.dto.*;
import data.searchdto.*;

public class LeaveDAOImpl implements LeaveDAO {

	@Override
	public ArrayList<LeaveDTO> findLeave(LeaveSearchDTO leaveSearchdto)
			throws LeaveDAOException, SQLException {
		ArrayList<LeaveDTO> list;
		PreparedStatement stmt = null;

		try {
			list = searchMatching(util.Database.getInstance().getConnection(),
					leaveSearchdto);

		} finally {
			if (stmt != null)
				stmt.close();
		}
		return list;
	}

	@Override
	public ArrayList<LeaveDTO> findLeavebyUser(int userid)
			throws LeaveDAOException, SQLException {
		// String sql = "SELECT * FROM `leave` WHERE isactive = 1 " +
		// "AND createdby = "+userid +
		// "ORDER BY userid ASC ";

		String sql = "SELECT l.id,l.leavetype,lt.name as LeaveTypeName,l.startdate,l.enddate,l.natureofleave,l.reason,l.workdissemination,"
				+ "l.contactdetails,l.status,u.name,nol.name as natureofleavename, ut.name as usertypename, ifnull(ldc.comment, '') as comment FROM `leave` as l left join leavedetailcomment ldc on l.id = ldc.leaveid, `user` as u, "
				+ "leavetype as lt, natureofleave as nol, usertype as ut WHERE l.createdby = u.id AND "
				+ "lt.id = l.leavetype AND nol.id = l.natureofleave AND u.usertype = ut.id AND l.createdby = "
				+ userid + "  ORDER BY userid ASC";

		ArrayList<LeaveDTO> searchResults = listQuery(util.Database
				.getInstance().getConnection().prepareStatement(sql));

		return searchResults;
	}

	@Override
	public ArrayList<LeaveDTO> findLeavebyUsers(short status,
			ArrayList<UserHierarchyDetailDTO> userids)
			throws LeaveDAOException, SQLException {
		String user = "";
		ArrayList<LeaveDTO> searchResults = null;

		for (UserHierarchyDetailDTO userId : userids) {
			user += " OR u.usertype = " + userId.getUserType();
		}

		user = !user.equalsIgnoreCase("") ? user.substring(4) : "";

		if (!user.equalsIgnoreCase("")) {
			String sql = "SELECT l.id,l.leavetype,lt.name as LeaveTypeName,l.startdate,l.enddate,l.natureofleave,l.reason,l.workdissemination,"
					+ "l.contactdetails,l.status,u.name,nol.name as natureofleavename, ut.name as usertypename, ifnull(ldc.comment, '') as comment FROM `leave` as l left join leavedetailcomment ldc on l.id = ldc.leaveid, `user` as u, leavetype as lt, natureofleave as nol, usertype as ut WHERE l.createdby = u.id AND "
					+ "lt.id = l.leavetype AND nol.id = l.natureofleave AND u.usertype = ut.id AND ("
					+ user
					+ ")"
					+ (status != util.Constants.LEAVE_APPLICATION_STATUS_ALL ? " AND l.status = "
							+ status
							: "") + " ORDER BY userid ASC";

			searchResults = listQuery(util.Database.getInstance()
					.getConnection().prepareStatement(sql));
		}

		return searchResults;
	}

	@Override
	public ArrayList<LeaveDTO> findAllLeave() throws LeaveDAOException,
			SQLException {

		// String sql =
		// "SELECT * FROM `leave` WHERE isactive = 1 ORDER BY id ASC ";
		String sql = "SELECT l.id,l.leavetype,lt.name as LeaveTypeName,l.startdate,l.enddate,l.natureofleave,l.reason,l.workdissemination,"
				+ "l.contactdetails,l.status,u.name, nol.name as natureofleavename, ut.name as usertypename, ifnull(ldc.comment, '') as comment  FROM `leave` as l left join leavedetailcomment ldc on l.id = ldc.leaveid, `user` as u, leavetype as lt, natureofleave as nol, usertype as ut WHERE l.createdby = u.id AND "
				+ "lt.id = l.leavetype AND nol.id = l.natureofleave AND u.usertype = ut.id ORDER BY userid ASC";

		ArrayList<LeaveDTO> searchResults = listQuery(util.Database
				.getInstance().getConnection().prepareStatement(sql));

		return searchResults;
	}

	@Override
	public void insertLeaveDetail(LeaveDTO LeaveDetail, HttpSession session)
			throws LeaveDAOException, SQLException {

		String sql = "";
		PreparedStatement stmt = null;

		try {
			sql = "INSERT INTO `leave` ( leavetype, startdate, "
					+ "enddate, natureofleave, reason, "
					+ "workdissemination, contactdetails, createdby, "
					+ "modifiedby, "
					+ "status, isactive) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			stmt.setInt(1, LeaveDetail.getLeaveType());
			stmt.setTimestamp(2, LeaveDetail.getStartDate());
			stmt.setTimestamp(3, LeaveDetail.getEndDate());
			stmt.setInt(4, LeaveDetail.getNatureOfLeave());
			stmt.setString(5, LeaveDetail.getReason());
			stmt.setString(6, LeaveDetail.getWorkDisemmination());
			stmt.setString(7, LeaveDetail.getContactDetails());
			stmt.setInt(8, util.CurrentUser.getCurrentUser(session).getId());
			stmt.setInt(9, util.CurrentUser.getCurrentUser(session).getId());
			//stmt.setInt(8, util.CurrentUser.getCurrentUser(session).getId());
			// stmt.setInt(9, util.CurrentUser.getCurrentUser(session).getId());
			stmt.setInt(10, LeaveDetail.getStatus());
			stmt.setInt(11, 1);

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void updateLeaveDetail(LeaveDTO LeaveDetail)
			throws LeaveDAOException, SQLException, NotFoundException {

		String sql = "UPDATE `leave` SET leavetype = ?, startdate = ?, enddate = ?, "
				+ "natureofleave = ?, reason = ?, workdissemination = ?, "
				+ "contactdetails = ?, "
				+ "status = ?  WHERE id = ? ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, LeaveDetail.getLeaveType());
			stmt.setTimestamp(2, LeaveDetail.getStartDate());
			stmt.setTimestamp(3, LeaveDetail.getEndDate());
			stmt.setInt(4, LeaveDetail.getNatureOfLeave());
			stmt.setString(5, LeaveDetail.getReason());
			stmt.setString(6, LeaveDetail.getWorkDisemmination());
			stmt.setString(7, LeaveDetail.getContactDetails());
			//stmt.setInt(8, LeaveDetail.getModifiedBy());
			// stmt.setTimestamp(9, util.CurrentDate.getCurrentTimeStamp());
			stmt.setInt(8, LeaveDetail.getStatus());

			stmt.setInt(9, LeaveDetail.getId());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void updateLeaveDetail(short status, int id)
			throws LeaveDAOException, SQLException, NotFoundException {

		String sql = "UPDATE `leave` SET status = ?  WHERE id = ? ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			stmt.setInt(1, status);
			stmt.setInt(2, id);

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void deleteLeaveDetail(LeaveDTO LeaveDetail)
			throws LeaveDAOException, SQLException, NotFoundException {

		String sql = "UPDATE `leave` SET isactive = 0 WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {

			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, LeaveDetail.getId());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	public ArrayList<LeaveDTO> searchMatching(Connection conn,
			LeaveSearchDTO valueObject) throws SQLException {

		ArrayList<LeaveDTO> searchResults;

		boolean first = true;
		StringBuffer sql = new StringBuffer(
				"SELECT l.id,l.leavetype,lt.name as LeaveTypeName,l.startdate,l.enddate,l.natureofleave,l.reason,l.workdissemination,"
						+ "l.contactdetails,l.status,u.name, nol.name as natureofleavename, ut.name as usertypename, ifnull(ldc.comment, '') as comment FROM `leave` as l left join leavedetailcomment ldc on l.id = ldc.leaveid, `user` as u, leavetype as lt, natureofleave as nol, usertype as ut WHERE l.createdby = u.id AND "
						+ "lt.id = l.leavetype AND l.natureofleave = nol.id AND u.usertype = ut.id ");
		// "SELECT *, '' as LeaveTypeName FROM `leave` WHERE isactive = 1 AND 1=1 ");

		if (valueObject.getLeaveType() != null
				&& valueObject.getLeaveType() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND l.leavetype = ").append(valueObject.getLeaveType())
					.append(" ");
		}

		if (valueObject.getStartDate() != null) {
			if (first) {
				first = false;
			}
			sql.append(" AND l.startedate >= ")
					.append(valueObject.getStartDate()).append(" ");
		}

		if (valueObject.getEndDate() != null) {
			if (first) {
				first = false;
			}
			sql.append(" AND l.enddate <= ").append(valueObject.getEndDate())
					.append(" ");
		}

		if (valueObject.getStatus() != null && valueObject.getStatus() != 0) {
			if (first) {
				first = false;
			}
			sql.append(" AND l.status = ").append(valueObject.getStatus())
					.append(" ");
		}

		if (valueObject.getUserId() != 0) {
			if (first) {
				first = false;
			}
			sql.append(" AND l.createdby = ").append(valueObject.getUserId())
					.append(" ");
		}

		sql.append("ORDER BY id ASC ");

		// System.out.println(sql);

		// Prevent accidential full table results.
		// Use loadAll if all rows must be returned.
		if (first)
			searchResults = new ArrayList<LeaveDTO>();
		else
			searchResults = listQuery(conn.prepareStatement(sql.toString()));

		return searchResults;
	}

	protected ArrayList<LeaveDTO> listQuery(PreparedStatement stmt)
			throws SQLException {

		ArrayList<LeaveDTO> searchResults = new ArrayList<LeaveDTO>();
		ResultSet result = null;
		LeaveDTO temp;

		try {
			result = util.Database.getInstance().executeQuery(stmt);

			while (result.next()) {
				temp = new LeaveDTO();

				temp.setId(result.getInt("id"));
				temp.setLeaveType(result.getShort("leavetype"));
				temp.setStartDate(result.getTimestamp("startdate"));
				temp.setEndDate(result.getTimestamp("enddate"));
				temp.setNatureOfLeave(result.getShort("natureofleave"));
				temp.setReason(result.getString("reason"));
				temp.setWorkDisemmination(result.getString("workdissemination"));
				temp.setContactDetails(result.getString("contactdetails"));
				// temp.setCreatedBy(result.getInt("createdby"));
				// temp.setCreatedOn(result.getTimestamp("createdon"));
				// temp.setModifiedBy(result.getInt("modifiedby"));
				// temp.setModifiedOn(result.getTimestamp("modifiedon"));
				temp.setStatus(result.getShort("status"));
				temp.setUsername(result.getString("name"));
				temp.setLeaveTypeName(result.getString("LeaveTypeName"));
				temp.setNatureOfLeaveName(result.getString("natureofleavename"));
				temp.setUserTypeName(result.getString("usertypename"));
				temp.setComment(result.getString("comment"));

				searchResults.add(temp);
			}

		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		}

		return searchResults;
	}
	@Override
	public float findremainingleave(int createdby, int leavetype, int usertype, String sql)
			throws SQLException {
		PreparedStatement stmt = null;
		ResultSet result = null;
		
		float count = 0;
		try {
			stmt = util.Database.getInstance().getConnection().prepareStatement(sql);
			result = util.Database.getInstance().executeQuery(stmt);

			while (result.next()) {
				count = Float.parseFloat(result.getString("count"));
			}
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		}

		return count;
	}

	@Override
	public ArrayList<LeaveDTO> findLeave(LeaveDTO leave)
			throws LeaveDAOException, SQLException {
		// TODO Auto-generated method stub
		return null;
	}
}
